/* Acquires a connection to a SchedulePRO database 
   and handles data retrieval / data writing for the Scheduler. */
/* Author: Craig Niles */
/* CS411, SchedulePRO Team Green */
/* Created: 3/19/2012 */
/* Last modified 4/22/2012 */
/* Good luck, have fun */

import java.util.*;
import java.sql.*;
import org.joda.time.*; // <-- because Sun's dates and times are busted

public class ScheduleProDataConnection {
    private Connection connection;

    boolean good;

    public boolean isGood() {
	return good;
    }

    public void putSchedulerMessage(int new_event_id, String message) {
	String putMessageString = 
	    "UPDATE new_event " +
	    "SET scheduler_message='" + message + "'" + 
	    " WHERE new_event_ID = " + new_event_id + ";";
	try {
	    Statement statement = connection.createStatement();
	    statement.execute(putMessageString);
	}
	catch (Exception e) {
	    System.err.println("In putSchedulerMessage() with query:");
	    System.err.println(putMessageString);
	    System.err.println(e);
	}
    }

    public void putValidEventSchedule(ValidEvent vEvent, int vEventId) throws SQLException {
	for(ISchedulable s : vEvent.schedulables) {
	    String writeValidEventInvite = "INSERT INTO valid_event_invitee_scheduling (valid_event_ID, invitee_ID) VALUES (" 
		+ vEventId + ", "  + s.getIID() +  ");";
	    Statement statement = connection.createStatement();
	    statement.execute(writeValidEventInvite);	    
	}
    }

    public void putValidEvent(ValidEvent vEvent, NewEvent nEvent) {
	DateMidnight dm = new DateMidnight(vEvent.start);
	DateTime start = new DateTime(vEvent.start);
	DateTime stop = new DateTime(vEvent.end);
	try {
	    String writeValidEventString = "INSERT INTO valid_event (new_event_id, event_date, start_time, stop_time, fitness) VALUES (";
	    writeValidEventString += nEvent.getId() + ", ";
	    writeValidEventString += "'" + dm.toString("yyyy-MM-dd") + "', ";
	    writeValidEventString += "'" + start.toString("yyyy-MM-dd HH-mm-ss") + "', ";
	    writeValidEventString += "'" + stop.toString("yyyy-MM-dd HH-mm-ss") + "', 100);";
	    Statement statement = connection.createStatement();
	    statement.execute(writeValidEventString);
	    ResultSet result = statement.executeQuery("SELECT LAST_INSERT_ID()");
	    result.next();
	    int vEventId = result.getInt(1);
	    statement.close();
	    putValidEventSchedule(vEvent, vEventId);
	}
	catch (Exception e) {
	    System.err.println(e.getMessage());
	}
    }

    /* Creates a list of Invitee objects and links them to the events (both Personal and SchedulePRO) given in inviteeEvents */
    public ArrayList<Invitee> getInvitees(int id, HashMap<String, Event> events) {
	try {
	    Statement statement = connection.createStatement();
	    ResultSet result;
	    int priority;
	    ArrayList<Integer> inviteList = getInviteList(id);
	    ArrayList<Invitee> inviteeList = new ArrayList<Invitee>();
	    for(Integer iID : inviteList) {
		String getEventQueryString = "SELECT event_ID FROM event_invitee_scheduling WHERE invitee_ID=" + iID.toString() + ";";
		String getInviteePriorityQueryString = 
		    "SELECT priority FROM new_event_invitee_scheduling WHERE invitee_ID="+iID.toString() + 
		    " AND new_event_ID=" + id + ";";
		String getPrivateEventCountQueryString = "SELECT COUNT(*) FROM invitee_private_event WHERE invitee_ID="+iID.toString()+";";

		result = statement.executeQuery(getInviteePriorityQueryString);

		if(result.next()) {
		    priority = result.getInt("priority");
		}
		else {
		    throw new Exception("No priority found for invitee");
		}
		

		ArrayList<Event> currentInviteeEvents = new ArrayList<Event>();
		/* Link public events
		 */
		result = statement.executeQuery(getEventQueryString);
		while(result.next()) {
		    String eID = "E" + result.getString(1);
		    Event ev = events.get(eID);
		    if(ev != null)
			currentInviteeEvents.add(events.get(eID));
		}
		
		/* Link private events
		 */
		result = statement.executeQuery(getPrivateEventCountQueryString);
		int privateEventCount = 0;
		if(result.next())
		    privateEventCount = result.getInt(1);
		else
		    throw new Exception("Private event query failed in getInvitees");
		for(int pIdCounter = 0; pIdCounter < privateEventCount; pIdCounter++) {
		    String pID = "P" + iID.toString() + "_" + pIdCounter;
		    Event ev = events.get(pID);
		    if(ev != null)
			currentInviteeEvents.add(events.get(pID));
		}

		inviteeList.add(new Invitee(iID, currentInviteeEvents, priority));
		
	    }
	    return inviteeList;
	}
	catch (Exception e) {
	    System.err.println(e.getMessage());
	}
	return null;
    }

    // Get a list of invitee objects associated with an event.
    public HashMap<String, Event> getInviteeEvents(int id) {
	try {
	    Statement statement = connection.createStatement();
	    ResultSet result;
	    // Prepare a string containing the list of invitees...
	    String inviteeList = "(";
	    ArrayList<Integer> invitees = getInviteList(id);
	    for(Integer i : invitees)
		inviteeList += i.toString() + ", ";
	    inviteeList += "-1);";

	    //System.out.println(inviteeList);
	    
	    String eventListQuery = "SELECT DISTINCT event_ID FROM event_invitee_scheduling WHERE invitee_ID IN " + inviteeList + ";";
	    result = statement.executeQuery(eventListQuery);
	    String events = "( ";
	    while(result.next()) {
		events += result.getString(1) + ", ";
	    }
	    events += "-1 )";

	    String query = "SELECT event_ID, start_time, stop_time FROM event WHERE event_ID IN " + events + ";";
	    result = statement.executeQuery(query);
	    HashMap<String, Event> eventList = new HashMap<String, Event>();
	    while(result.next()) {
		String eID = "E" + result.getString(1);
		eventList.put(eID, new Event(eID, result.getTimestamp(2).getTime(), result.getTimestamp(3).getTime(), Event.EventType.PUBLIC));
	    }	    
	    return eventList;
	}
	catch (Exception e) {
	    System.err.println(e.getMessage());
	}
	return null;
    }



    /* Assign event ID as P|<invitee_id>|<invitee_entry_index>*/
    public HashMap<String, Event> getInviteePrivateEvents(int id) {
	try {
	    Statement statement = connection.createStatement();
	    ResultSet result;
	    // Prepare a string containing the list of invitees...
	    String inviteeList = "(";
	    ArrayList<Integer> invitees = getInviteList(id);
	    for(Integer i : invitees)
		inviteeList += i.toString() + ", ";
	    inviteeList += "-1)";

	    //System.out.println(inviteeList);
	    
	    String query = "SELECT * FROM invitee_private_event WHERE invitee_ID IN " + inviteeList +
		" ORDER BY invitee_ID, start_time;";

	    HashMap<String, Event> eventList = new HashMap<String, Event>();

	    result = statement.executeQuery(query);
	    /* Creating a rolling counter on the returned events...
	     * use the counter in assigning a unique key for the private event
	     * reset the counter for each invitee
	     */
	    int counter = -1; 
	    int current = -1; // no negative invitee IDs
	    while(result.next()) {
		int iId = result.getInt(1);
		if(current != iId ) {
		    current = iId;
		    counter = 0;
		} else ++counter;
		String pID = "P" + result.getString(1) + "_" + counter;
		eventList.put(pID, new Event(pID, result.getTimestamp(2).getTime(), result.getTimestamp(3).getTime(), Event.EventType.PRIVATE));
		}
	    return eventList;
	}
	catch (Exception e) {
	    System.err.println(e.getMessage());
	}
	return null;
    }

    public ArrayList<Integer> getInviteList(int id) {
	try {
	    String query = "SELECT invitee_ID FROM new_event_invitee_scheduling WHERE new_event_ID = " + id;
	    Statement statement = connection.createStatement();
	    ResultSet result = statement.executeQuery(query);
	    ArrayList<Integer> inviteList = new ArrayList<Integer>();
	    while(result.next()) {
		inviteList.add(Integer.valueOf(result.getInt("invitee_ID")));
	    }
	    return inviteList;
	}
	catch (Exception e) {
	    System.out.println(e.getMessage());
	}
	return null;
    }

    public ArrayList<Integer> getResourceRequestList(int id) {
	try {
	    String query = "SELECT resource_ID FROM new_event_resource_scheduling WHERE new_event_ID = " + id;
	    Statement statement = connection.createStatement();
	    ResultSet result = statement.executeQuery(query);
	    ArrayList<Integer> inviteList = new ArrayList<Integer>();
	    while(result.next()) {
		inviteList.add(Integer.valueOf(result.getInt("resource_ID")));
	    }
	    return inviteList;
	}
	catch (Exception e) {
	    System.err.println(e.getMessage());
	}
	return null;
    }

    public NewEvent getNewEventEntry(int id) {
	try {
	    String query = "SELECT * FROM new_event WHERE new_event_id = " + id + ";";
	    Statement statement = connection.createStatement();
	    ResultSet result = statement.executeQuery(query);
	    if(result.next()) {
		java.sql.Time time = result.getTime("meeting_length");
		int length = (time.getHours() * 60 + time.getMinutes()) * 60 * 1000;
		NewEvent newEvent = new NewEvent(result.getInt("new_event_id"),
						 result.getString("new_event_name"),
						 result.getInt("coordinator_id"),
						 new DateTime(result.getTimestamp("date_range_start").getTime()),
						 new DateTime(result.getTimestamp("date_range_stop").getTime()),
						 length);
		return newEvent;
	    }
	}
	catch (Exception e) {
	    System.out.println("In getNewEventEntry(): ");
	    System.out.println(e.getMessage());
	}
	return null;
    }
    
    public void close() {
	try {
	    connection.close();
	}
	catch (Exception e) {
	    System.out.println(e.getMessage());
	}
    }

    String error = "No error";

    public String getMessage() { return error; }

    ScheduleProDataConnection(String url, String user, String pass) {
	good = true;
	try {
	    connection = DriverManager.getConnection(url,user,pass);
	}
	catch(SQLException eSQL) {
	    good = false;
	    error = eSQL.getMessage();
	}
    }
}
